package repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import utils.SQLFactory;
import classes.Frequency;
import classes.Iterator;
import exceptions.RepositoryException;

public class FrequencyRepository
{
	private static FrequencyRepository	instance;
	private Connection					connection;
	private PreparedStatement			addStatement;
	private PreparedStatement			containsStatement;
	private PreparedStatement			removeStatement;
	private PreparedStatement			editStatement;
	private PreparedStatement			getStatement;
	private PreparedStatement			iteratorStatement;
	private PreparedStatement			sizeStatement;

	private FrequencyRepository() throws RepositoryException
	{
		try
		{
			this.connection = SQLFactory.getConnectionInstance();
			this.init();
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
	}

	public static FrequencyRepository getInstance() throws RepositoryException
	{
		if ( instance == null )
		{
			instance = new FrequencyRepository();
		}

		return instance;
	}

	private void init() throws RepositoryException
	{
		try
		{
			if ( connection != null && !connection.isClosed() )
			{
				this.addStatement = connection.prepareStatement( "insert into frequencia (membro_id, dia) values(?,?)" );
				this.containsStatement = connection.prepareStatement( "select count(*) from frequencia where id = ?" );
				this.removeStatement = connection.prepareStatement( "delete from frequencia where id = ?" );
				this.editStatement = connection.prepareStatement( "update frequencia set membro_id = ?, dia = ? where id = ?" );
				this.getStatement = connection.prepareStatement( "select * from frequencia where id = ?" );
				this.iteratorStatement = connection.prepareStatement( "select * from frequencia" );
				this.sizeStatement = connection.prepareStatement( "select count(*) from frequencia" );

				connection.setAutoCommit( false );
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}

	public void add( Frequency frequency ) throws RepositoryException
	{
		try
		{
			addStatement.clearParameters();
			addStatement.setInt( 1, frequency.getMemberId() );
			addStatement.setDate( 2, frequency.getDay() );
			addStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public void close() throws RepositoryException
	{
		try
		{
			if ( !connection.isClosed() )
			{
				this.addStatement.close();
				this.editStatement.close();
				this.getStatement.close();
				this.iteratorStatement.close();
				this.removeStatement.close();
				this.sizeStatement.close();
				this.connection.close();
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}
	
	public boolean contains( int id ) throws RepositoryException
	{
		boolean ret;
		
		try
		{
			containsStatement.clearParameters();
			containsStatement.setInt( 1, id );
			ResultSet result = containsStatement.executeQuery();

			ret = false;
			if ( result.next() )
			{
				int count = result.getInt( 1 );
				ret = count == 1;
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}

	public void edit( Frequency frequency ) throws RepositoryException
	{
		try
		{
			editStatement.clearParameters();
			editStatement.setInt( 1, frequency.getMemberId() );
			editStatement.setDate( 2, frequency.getDay() );
			editStatement.setInt( 3, frequency.getId() );
			editStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}
	
	public Iterator< Frequency > find( Frequency frequency ) throws RepositoryException
	{
		Iterator< Frequency > ret;
		
		ret = null;
		
		return ret;
	}

	public Frequency get( int id ) throws RepositoryException
	{
		Frequency ret;

		try
		{
			getStatement.clearParameters();
			getStatement.setInt( 1, id );
			ResultSet result = getStatement.executeQuery();

			ret = null;
			if ( result.next() )
			{
				int memberId = result.getInt( "membro_id" );
				Date day = result.getDate( "dia" );
				ret = new Frequency( memberId, new utils.Date( day.getTime() ) );
				ret.setId( id );
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public Iterator< Frequency > getIterator() throws RepositoryException
	{
		Iterator< Frequency > ret;

		try
		{
			Vector< Frequency > vetor = new Vector< Frequency >();
			ResultSet result = iteratorStatement.executeQuery();
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				int memberId = result.getInt( "membro_id" );
				Date day = result.getDate( "dia" );
				Frequency frequency = new Frequency( memberId, new utils.Date ( day.getTime() ) );
				frequency.setId( id );
				vetor.add( frequency );
			}
			result.close();

			ret = new Iterator< Frequency >( vetor );
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public void remove( int id ) throws RepositoryException
	{
		try
		{
			removeStatement.clearParameters();
			removeStatement.setInt( 1, id );
			removeStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public int size() throws RepositoryException
	{
		int ret;

		try
		{
			ResultSet result = sizeStatement.executeQuery();
			result.next();
			ret = result.getInt( 1 );
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}
}
